package com.itheima.service;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.User;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<User> findAll() {
        return userMapper.selectAll();
    }

    public List<User> findPage(Integer page, Integer pageSize) {
        PageHelper.startPage(page, pageSize);  //开启分页
        Page<User> userPage = (Page<User>) userMapper.selectAll(); //实现查询
        return userPage.getResult();
    }


    public void downLoadXlsByJxl(HttpServletResponse response) throws IOException, WriteException {
        List<User> userList = userMapper.selectAll();
        //获取一个输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //创建一个工作薄
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
        //创建一个工作表
        WritableSheet sheet = workbook.createSheet("用户表", 1);
        //处理标题
        String[] titles = {"编号", "姓名", "手机号", "入职日期", "现住址"};
        Label label = null;
        //调整列宽
        sheet.setColumnView(0, 3);
        sheet.setColumnView(1, 10);
        sheet.setColumnView(2, 15);
        sheet.setColumnView(3, 15);
        sheet.setColumnView(4, 30);
        //格式化日期
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");


        for (int i = 0; i < titles.length; i++) {
            String title = titles[i];
            label = new Label(i, 0, title);
            sheet.addCell(label);
        }
        int rowsIndex = 1;
        for (int i = 0; i < userList.size(); i++) {
            sheet.addCell(new Label(0, rowsIndex, String.valueOf(userList.get(i).getId())));
            sheet.addCell(new Label(1, rowsIndex, userList.get(i).getUserName()));
            sheet.addCell(new Label(2, rowsIndex, userList.get(i).getPhone()));
            sheet.addCell(new Label(3, rowsIndex, format.format(userList.get(i).getHireDate())));
            sheet.addCell(new Label(4, rowsIndex, userList.get(i).getAddress()));
            rowsIndex++;
        }
        //设置文件名
        String filename = "用户表.xls";
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
        response.setContentType("application/vnd.ms-excel");
        workbook.write();
        workbook.close();
        outputStream.close();
    }

    public void uploadExcel(MultipartFile file) throws Exception {
        //  用户名	手机号	省份	城市	工资	入职日期	出生日期	现住地址
        org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook( file.getInputStream());
        // 读取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 获取最后一行的索引
        int lastRowNum = sheet.getLastRowNum();
        Row row = null;
        //格式化日期
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        User user = null;
        ArrayList<User> users = new ArrayList<>();
        for (int i = 1; i <= lastRowNum; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                String username = row.getCell(0).getStringCellValue();
                String phone = row.getCell(1).getStringCellValue();
                String province = row.getCell(2).getStringCellValue();
                String city = row.getCell(3).getStringCellValue();
                double salary = row.getCell(4).getNumericCellValue();
                Date hireDate =  simpleDateFormat.parse(row.getCell(5).getStringCellValue());
                Date birthday = simpleDateFormat.parse(row.getCell(6).getStringCellValue());
                String address = row.getCell(7).getStringCellValue();
                user = new User(username,phone,province,city,salary,hireDate,birthday,address);
                System.out.println("user = " + user);
                System.out.println("=======================");
                users.add(user);
                userMapper.insert(user);

            }
        }
    }

    public void downLoadXlsxByPoi(HttpServletResponse response) {
        List<User> userList = userMapper.selectAll();
        
    }
}
